package com.atsmart.app.sql;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.atsmart.app.model.TaskInfo;
import com.atsmart.app.model.TaskListInfo;
/**
 * TODO 任务表
 */
public class TaskInfoSqlHelper {

	private SQLiteDatabase db = null;

	/**
	 * 打开数据库
	 */
	public void open(SqlHelper dh) {
		// dh = new SqlHelper(null, "atsmart", null,SqlHelper.getDBVersion());
		db = dh.getWritableDatabase();
	}

	public synchronized void addAttribute(TaskInfo at) {
		if (at == null) {
			return;
		}
		String strSql = "insert or replace into task_info (date,name,content,state,templateID,polId,lon,lat,template_num,tid) values(?,?,?,?,?,?,?,?,?,?);";
		try {
			SQLiteStatement stm = db.compileStatement(strSql);
			stm.bindString(1, at.getDate()== null ? "" : at.getDate());
			stm.bindString(2, at.getName()== null ? "" : at.getName());
			stm.bindString(3, at.getContent()== null ? "" : at.getContent());
			stm.bindLong(4, at.getState());
			stm.bindString(5,
					at.getTemplateID() == null ? "" : at.getTemplateID());
			stm.bindLong(6, at.getPolId());
			stm.bindDouble(7, at.getLon());
			stm.bindDouble(8, at.getLat());
			stm.bindDouble(9, at.getTemplateNum());
			stm.bindLong(10, at.getId());
			stm.execute();
			stm.clearBindings();
			stm.close();
		} catch (Exception e) {
			// will not happen
		}
	}

	public TaskListInfo getTaskList(int begin, int pagesize) {
		// begin = 2;
		// end= 6;
		String sql = " select date,name,content,state,templateID,polId,lon,lat,template_num,tid from task_info order by  date desc limit "
				+ begin + "," + pagesize;
		Cursor cursor = db.rawQuery(sql, null);
		TaskInfo task = null;
		TaskListInfo list = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				list = new TaskListInfo();
				List<TaskInfo> li = new ArrayList<TaskInfo>();
				for (int i = 0; i < cursor.getCount(); i++) {
					task = new TaskInfo();
					task.setDate(cursor.getString(0));
					task.setName(cursor.getString(1));
					task.setContent(cursor.getString(2));
					task.setState(cursor.getInt(3));
					task.setTemplateID(cursor.getString(4));
					task.setPolId(cursor.getInt(5));
					task.setLon(cursor.getDouble(6));
					task.setLat(cursor.getDouble(7));
					task.setTemplateNum(cursor.getInt(8));
					task.setId(cursor.getInt(9));
					li.add(task);
					cursor.moveToNext();
				}
				list.setTasks(li);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return list;
	}
	public TaskListInfo getTaskInfo(int task_id) {
		// begin = 2;
		// end= 6;
		String sql = " select date,name,content,state,templateID,polId,lon,lat,template_num,tid from task_info  where tid="+task_id;
		Cursor cursor = db.rawQuery(sql, null);
		TaskInfo task = null;
		TaskListInfo list = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				list = new TaskListInfo();
				List<TaskInfo> li = new ArrayList<TaskInfo>();
				for (int i = 0; i < cursor.getCount(); i++) {
					task = new TaskInfo();
					task.setDate(cursor.getString(0));
					task.setName(cursor.getString(1));
					task.setContent(cursor.getString(2));
					task.setState(cursor.getInt(3));
					task.setTemplateID(cursor.getString(4));
					task.setPolId(cursor.getInt(5));
					task.setLon(cursor.getDouble(6));
					task.setLat(cursor.getDouble(7));
					task.setTemplateNum(cursor.getInt(8));
					task.setId(cursor.getInt(9));
					li.add(task);
					cursor.moveToNext();
				}
				list.setTasks(li);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return list;
	}
	public void deleteAttribute(int taskId) {
		db.execSQL("delete from task_info where tid="+taskId);
	}
	public void delete() {
		db.execSQL("delete from task_info ");
	}
	
	public int getTemNumByTaskId(int task_id){
		String sql = "select template_num from task_info where tid=" + task_id;
		Cursor cursor = db.rawQuery(sql, null);
		int count = 0;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				count = cursor.getInt(0);
			}
		} catch (Exception e) {
			count = 0;
		}
		return count;
	}
	
	public int getTaskListCount() {
		String sql = " select date,name,content,state,templateID,polId,lon,lat from task_info ";
		Cursor cursor = db.rawQuery(sql, null);
		TaskInfo task = null;
		TaskListInfo list = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				list = new TaskListInfo();
				List<TaskInfo> li = new ArrayList<TaskInfo>();
				list.setTasks(new ArrayList<TaskInfo>());
				for (int i = 0; i < cursor.getCount(); i++) {
					task = new TaskInfo();
					task.setDate(cursor.getString(0));
					task.setName(cursor.getString(1));
					task.setContent(cursor.getString(2));
					task.setState(cursor.getInt(3));
					task.setTemplateID(cursor.getString(4));
					task.setPolId(cursor.getInt(5));
					task.setLon(cursor.getDouble(6));
					task.setLat(cursor.getDouble(7));
					// task.setUpdate(cursor.getString(8));
					li.add(task);
					cursor.moveToNext();
				}
				list.setTasks(li);
			}
		} catch (Exception e) {
			return 0;
		} finally {
			cursor.close();
		}
		if (list == null) {
			return 0;
		}
		return list.getTasks().size();
	}

	public void updateTemplate(int task_id, int num) {
		ContentValues cv = new ContentValues();
		cv.put("template_num", num);
		db.update("task_info", cv, " tid=" + task_id, null);
	}
}
